﻿using AutoMapper;
using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;
using Org.BouncyCastle.Crypto;
using QMERP.Domain.Dtos;
using QMERP.Domain.Entities;
using QMERP.Domain.Enums;
using QMERP.Domain.PrintModels;
using QMERP.Infrastructure.CustomException;
using QMERP.Infrastructure.EFCore;
using QMERP.Infrastructure.EFCore.Extensions;
using QMERP.Infrastructure.Extensions;
using QMERP.Infrastructure.Security;
using QMERP.Infrastructure.Tools;
using System.Data;

namespace QMERP.Application.Services
{
    public class BulkOrderService : IBulkOrderService
    {
        private readonly IMapper _mapper;
        private readonly QMERPDbContext _db;
        private readonly ISerialNumberService _serialNumberSvc;
        private readonly ICurrentUser _currentUser;
        private readonly ILeaveStockOrderService _leaveStockOrderSvc;
        private readonly IAttachmentService _attachmentSvc;
        public BulkOrderService(IMapper mapper, QMERPDbContext db, ISerialNumberService serialNumberSvc, ICurrentUser currentUser, ILeaveStockOrderService leaveStockOrderSvc, IAttachmentService attachmentSvc)
        {
            _mapper = mapper;
            _db = db;
            _serialNumberSvc = serialNumberSvc;
            _currentUser = currentUser;
            _leaveStockOrderSvc = leaveStockOrderSvc;
            _attachmentSvc = attachmentSvc;
        }
        /// <summary>
        /// 批量订单窗体
        /// </summary>
        /// <returns></returns>
        public async Task<PageResponse<BulkOrderResponse>> Query(PageRequest<BulkOrderRequest> request)
        {
            var response = new PageResponse<BulkOrderResponse>();
            var query = Where(request);
            var list = await query.Paging(request.PageIndex, request.PageSize).ToListAsync();
            response.TotalCount = await query.CountAsync();
            response.PageIndex = request.PageIndex;
            response.PageSize = request.PageSize;
            response.List = _mapper.Map<List<BulkOrderResponse>>(list);
            return response;
        }
        /// <summary>
        /// 获取id的批量订单详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<BulkOrderResponse> Detail(long id)
        {
            var model = await _db.BulkOrder
                .Include(x => x.Customer).Include(x => x.Contract)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.BulkOrderDetail_LensDetails)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.BulkOrderDetail_MonofocalMaterialDetails)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.BulkOrderDetail_BifocalMaterialDetails)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Supplier)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Warehouse)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Contract_Lens).ThenInclude(x => x!.Lens).AsNoTracking().FindByIdAsync(id);
            var response = _mapper.Map<BulkOrderResponse>(model);
            if (model != null)
            {
                var attachments = await _db.Attachment.Where(x => x.OwnerId == model.Id && x.OwnerType == AttachmentOwnerTypeEnum.BulkOrder.ToString()).ToListAsync();
                response.Attachments = _mapper.Map<List<AttachmentResponse>>(attachments);
                response.AttachmentIds = new List<long>();
                attachments.ForEach(x => response.AttachmentIds.Add(x.Id));
            }

            return response;
        }
        /// <summary>
        /// 批量订单保存
        /// </summary>
        /// <param name="request"></param>
        /// <param name="isCheck"></param>
        /// <returns></returns>
        /// <exception cref="ApiException"></exception>
        public async Task<BulkOrderResponse> Save(BulkOrderSaveRequest request, bool isCheck = false)
        {
            if (request.BulkOrderDetails.Count <= 0)
                throw new ApiException("请填写镜种信息!");

            if (request.CustomerOrderNumber.IsNullOrWhiteSpace())
                throw new ApiException($"客户单号不能为空!");

            if (await _db.BulkOrder.AnyAsync(x => x.CustomerOrderNumber.ToLower() == request.CustomerOrderNumber.ToLower() && x.Id != request.Id))
                throw new ApiException($"客户单号{request.CustomerOrderNumber}已存在!");

            for (int i = 0; i < request.BulkOrderDetails.Count; i++)
            {
                string col = $"第{i + 1}行:";
                var detail = request.BulkOrderDetails[i];

                var stock = await (from a in _db.Stock.Include(x => x.StockDetails).Include(x => x.Warehouse)
                                   join b in _db.Contract_Lens on a.LensId equals b.LensId
                                   join c in _db.Lens_Supplier on a.LensId equals c.LensId
                                   where b.ContractId == request.ContractId && c.SupplierId == detail.SupplierId && a.WarehouseId == detail.WarehouseId && b.Id == detail.Contract_LensId
                                   select a
                     ).FirstOrDefaultAsync();

                if (stock == null)
                    throw new ApiException($"{col}此供应商仓库的库存镜种不存在!");

                if (request.Type == BulkOrderTypeEnum.Product.ToString())
                {
                    if (detail.LensDetails.Count <= 0)
                        throw new ApiException($"{col}请导入XY表!");

                    if (detail.LensDetails.Sum(s => s.Qty) <= 0)
                        throw new ApiException($"{col}XY表总片数为0,请检查!");

                    var contract_Lens = await _db.Contract_Lens.Include(x => x.Lens).FindByIdAsync(detail.Contract_LensId);

                    if (!detail.LensDetails.InRange(_mapper.Map<Contract_LensResponse>(contract_Lens)))
                        throw new ApiException($"{col}XY表度数超出镜种范围!");

                    detail.LensDetails = detail.LensDetails.Where(x => x.Qty > 0).ToList();
                    detail.BulkOrderDetail_MonofocalMaterialDetails = new();
                    detail.BulkOrderDetail_BifocalMaterialDetails = new();
                    foreach (var lensDetail in detail.LensDetails)
                    {
                        var stockDetail = stock.GetByRange(lensDetail.DegreesType, lensDetail.SPH, lensDetail.CYL);
                        if (stockDetail.Qty - lensDetail.Qty < 0)
                        {
                            throw new ApiException($"{col}仓库{stock.Warehouse?.Code}镜种{contract_Lens?.Lens?.SalesLensCode}度数SPH:{lensDetail.SPH},CYL{lensDetail.CYL}库存片数不足扣库");
                        }
                    }
                }
                else if (request.Type == BulkOrderTypeEnum.MonofocalMaterial.ToString())
                {
                    if (detail.BulkOrderDetail_MonofocalMaterialDetails.Count <= 0)
                        throw new ApiException($"{col}请导入单光毛料数据!");

                    if (detail.BulkOrderDetail_MonofocalMaterialDetails.Sum(s => s.Qty) <= 0)
                        throw new ApiException($"{col}单光毛料数据总片数为0,请检查!");

                    detail.BulkOrderDetail_MonofocalMaterialDetails = detail.BulkOrderDetail_MonofocalMaterialDetails.Where(x => x.Qty > 0).ToList();
                    detail.LensDetails = new();
                    detail.BulkOrderDetail_BifocalMaterialDetails = new();
                }
                else if (request.Type == BulkOrderTypeEnum.BifocalMaterial.ToString())
                {
                    if (detail.BulkOrderDetail_BifocalMaterialDetails.Count <= 0)
                        throw new ApiException($"{col}请导入双光毛料数据!");

                    if (detail.BulkOrderDetail_BifocalMaterialDetails.Sum(s => s.Qty) <= 0)
                        throw new ApiException($"{col}双光毛料数据总片数为0,请检查!");

                    detail.BulkOrderDetail_BifocalMaterialDetails = detail.BulkOrderDetail_BifocalMaterialDetails.Where(x => x.Qty > 0).ToList();
                    detail.LensDetails = new();
                    detail.BulkOrderDetail_MonofocalMaterialDetails = new();
                }
            }

            BulkOrder? model;
            if (request.Id == null)
            {
                string number = await _serialNumberSvc.GetSerialNumber(SerialNumberTypeEnum.BulkOrder);
                model = _mapper.Map<BulkOrder>(request);
                model.Number = number;
                //model.SequenceNumber = GetNewSequenceNumber(request.SupplierId, request.WarehouseId);
                model.Status = PurchaseOrderStatusEnum.Pending.ToString();
                await _db.AddAsync(model);
            }
            else
            {
                model = await _db.BulkOrder.Include(x => x.BulkOrderDetails).FindByIdAsync(request.Id.Value);
                if (model == null)
                    throw new ApiException("批量订单不存在!");
                model.CustomerOrderNumber = request.CustomerOrderNumber;
                model.OrderRef = request.OrderRef;
                model.CustomerId = request.CustomerId;
                model.ContractId = request.ContractId;
                model.DeliveryOn = request.DeliveryOn;
                model.OutRank = request.OutRank;
                model.Remark = request.Remark;
                model.BulkOrderDetails = _mapper.Map<List<BulkOrderDetail>>(request.BulkOrderDetails);
            }
            await _db.SaveChangesAsync();
            if (isCheck)
            {
                model = await _db.BulkOrder.Include(x => x.Contract).Include(x => x.Customer)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.BulkOrderDetail_LensDetails)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Supplier)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Warehouse)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Contract_Lens).ThenInclude(x => x!.Lens).FindByIdAsync(model.Id);
                if (model != null)
                {
                    await Check(model);
                    await _db.SaveChangesAsync();
                }
            }
            if (model != null)
            {
                if (request.AttachmentIds != null && request.AttachmentIds.Count > 0)
                {
                    AttachmentSaveRequest attachmentSaveRequest = new()
                    {
                        OwnerId = model.Id,
                        OwnerType = AttachmentOwnerTypeEnum.BulkOrder.ToString(),
                        AttachmentIds = request.AttachmentIds
                    };
                    await _attachmentSvc.Save(attachmentSaveRequest);
                }
            }
            return _mapper.Map<BulkOrderResponse>(model);
        }
        /// <summary>
        /// 批量订单审核
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task Check(long[] ids)
        {
            if (ids == null)
                throw new ApiException("id不能为空!");

            var list = await _db.BulkOrder.Where(x => ids.Contains(x.Id)).ToListAsync();
            foreach (var order in list)
            {
                if (order.Status != BulkOrderStatusEnum.Pending.ToString())
                    throw new ApiException($"批量订单{order.Number}不能审核!");

                await Check(order);
            }
            await _db.SaveChangesAsync();
        }
        /// <summary>
        /// 批量订单弃审
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task UnCheck(long[] ids)
        {
            if (ids == null)
                throw new ApiException("id不能为空!");

            var list = await _db.BulkOrder.Where(x => ids.Contains(x.Id)).ToListAsync();
            foreach (var order in list)
            {
                if (order.Status != BulkOrderStatusEnum.Delivery.ToString())
                    throw new ApiException($"批量订单{order.Number}不能弃审!");

                if (await _db.LeaveStockOrder.AnyAsync(x => x.BusinessOrderType == LeaveStockBusinessOrderTypeEnum.BulkOrder.ToString() && x.BusinessOrderId == order.Id && x.Status != LeaveStockOrderStatusEnum.Canceled.ToString()))
                    throw new ApiException($"批量订单{order.Number}不能弃审,已添加出库单!");

                order.Status = BulkOrderStatusEnum.Pending.ToString();
                order.CheckedBy = null;
                order.CheckedByName = null;
                order.CheckedOn = null;
            }
            await _db.SaveChangesAsync();
        }
        /// <summary>
        /// 批量订单取消
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task Cancel(long[] ids)
        {
            if (ids == null)
                throw new ApiException("id不能为空!");

            var list = await _db.BulkOrder.Where(x => ids.Contains(x.Id)).ToListAsync();
            foreach (var order in list)
            {
                if (order.Status != BulkOrderStatusEnum.Pending.ToString() || order.Status != BulkOrderStatusEnum.Delivery.ToString())
                    throw new ApiException($"批量订单{order.Number}不能取消!");

                if (await _db.LeaveStockOrder.AnyAsync(x => x.BusinessOrderType == LeaveStockBusinessOrderTypeEnum.BulkOrder.ToString() && x.BusinessOrderId == order.Id && x.Status != LeaveStockOrderStatusEnum.Canceled.ToString()))
                    throw new ApiException($"批量订单{order.Number}不能取消,已添加出库单!");

                order.Status = BulkOrderStatusEnum.Canceled.ToString();
            }
            await _db.SaveChangesAsync();
        }
        /// <summary>
        /// 批量订单完成
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task Finish(long[] ids)
        {
            if (ids == null)
                throw new ApiException("id不能为空!");

            var list = await _db.BulkOrder.Where(x => ids.Contains(x.Id)).ToListAsync();
            foreach (var order in list)
            {
                if (order.Status != BulkOrderStatusEnum.AllDelivered.ToString())
                    throw new ApiException($"批量订单{order.Number}不能完成!");

                order.Status = BulkOrderStatusEnum.Finished.ToString();
                order.FinishedBy = _currentUser.Id;
                order.FinishedByName = _currentUser.Name;
                order.FinishedOn = DateTime.Now;
            }
            await _db.SaveChangesAsync();
        }
        /// <summary>
        /// 获取批量订单主要信息列表
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public async Task<List<BulkOrderMainInfoResponse>> GetBulkOrderMainInfos(PageRequest<BulkOrderRequest> request)
        {
            var orders = await Where(request).ToListAsync();
            var details = orders.SelectMany(s => s.BulkOrderDetails).ToList();
            return _mapper.Map<List<BulkOrderMainInfoResponse>>(details);
        }
        /// <summary>
        /// 批量出库
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task<List<LeaveStockOrderResponse>> BatchLeaveStock(long[] ids)
        {
            var bulkOrders = await _db.BulkOrder.Include(x => x.Contract).Include(x => x.Customer)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.BulkOrderDetail_LensDetails)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Supplier)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Warehouse)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Contract_Lens).ThenInclude(x => x!.Lens).AsNoTracking().Where(x => ids.Contains(x.Id)).ToListAsync();
            var orders = new List<LeaveStockOrderResponse>();
            foreach (var bulkOrder in bulkOrders)
            {
                if (bulkOrder.Type != BulkOrderTypeEnum.Product.ToString())
                    throw new ApiException($"批量订单{bulkOrder.Number}类型不是成品不能出库!");

                var leaveStockOrders = await _db.LeaveStockOrder.GetByBusinessOrderIdAsync(LeaveStockBusinessOrderTypeEnum.BulkOrder.ToString(), bulkOrder.Id).ToListAsync();

                var order = new LeaveStockOrderSaveRequest()
                {
                    BusinessOrderId = bulkOrder.Id,
                    BusinessOrderNumber = bulkOrder.Number,
                    BusinessOrderType = LeaveStockBusinessOrderTypeEnum.BulkOrder.ToString(),
                    BusinessType = LeaveStockBusinessTypeEnum.SalesLeaveStock.ToString(),
                };
                foreach (var bulkOrderDetail in bulkOrder.BulkOrderDetails)
                {
                    foreach (var lensDetail in bulkOrderDetail.BulkOrderDetail_LensDetails)
                    {
                        var qty = leaveStockOrders.SelectMany(s => s.LeaveStockOrderDetails).SelectMany(s => s.LeaveStockOrderDetail_LensDetails).Where(x => x.DegreesType == lensDetail.DegreesType && x.SPH == lensDetail.SPH && x.CYL == lensDetail.CYL).Sum(s => s.Qty);
                        lensDetail.Qty = lensDetail.Qty - qty;
                    }
                    LeaveStockOrderDetailSaveRequest leaveStockOrderDetail = new()
                    {
                        WarehouseId = bulkOrderDetail.Warehouse!.Id,
                        LensId = bulkOrderDetail.Contract_Lens!.LensId,
                        LensDetails = _mapper.Map<List<LensDetailResponse>>(bulkOrderDetail.BulkOrderDetail_LensDetails)
                    };
                    order.LeaveStockOrderDetails.Add(leaveStockOrderDetail);
                }
                orders.Add(await _leaveStockOrderSvc.Save(order));
            }
            return orders;
        }
        /// <summary>
        /// 获取发票数据
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task<List<BulkOrderInvoice>> GetInvoices(long[] ids)
        {
            var orders = await _db.BulkOrder
                .Include(x => x.Customer).Include(x => x.Contract)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.BulkOrderDetail_LensDetails)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Supplier)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Warehouse)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Contract_Lens).ThenInclude(x => x!.Lens).AsNoTracking().Where(x => ids.Contains(x.Id)).ToListAsync();
            if (orders.Count <= 0)
            {
                throw new ApiException("批量订单不存在!");
            }
            List<BulkOrderInvoice> bulkOrderInvoices = new();
            foreach (var order in orders)
            {
                if (order.Type != BulkOrderTypeEnum.Product.ToString())
                    throw new ApiException($"批量订单{order.Number}类型不是成品!");

                BulkOrderInvoice bulkOrderInvoice = new()
                {
                    OrderNumber = order.Number,
                    CustomerOrderNumber = order.CustomerOrderNumber,
                    TrayNumber = order.TrayNumber,
                    CustomerCode = order.Customer!.Code,
                    CustomerName = order.Customer!.Name,
                    CreatedByName = order.CreatedByName,
                    CreatedOn = order.CreatedOn,
                    Remark = order.Remark,
                };
                foreach (var orderDetail in order.BulkOrderDetails)
                {
                    var detail = new BulkOrderInvoiceDetail()
                    {
                        WarehouseCode = orderDetail.Warehouse!.Code,
                        WarehouseName = orderDetail.Warehouse!.Name,
                        SalesLensCode = orderDetail.Contract_Lens!.Lens!.SalesLensCode,
                        FactoryLensCode = orderDetail.Contract_Lens!.Lens!.FactoryLensCode,
                    };
                    var sphs = orderDetail.BulkOrderDetail_LensDetails.Select(s => s.SPH).Distinct().ToList();
                    foreach (var sph in sphs)
                    {
                        var ld = new BulkOrderInvoiceDetail_LensDetail
                        {
                            SPH = sph
                        };
                        var lensDetails = orderDetail.BulkOrderDetail_LensDetails.Where(x => x.SPH == sph).ToList();
                        foreach (var lensDetail in orderDetail.BulkOrderDetail_LensDetails)
                        {
                            if (lensDetail.CYL == 0)
                            {
                                ld.CELL0 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -25)
                            {
                                ld.CELL25 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -50)
                            {
                                ld.CELL50 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -75)
                            {
                                ld.CELL75 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -100)
                            {
                                ld.CELL100 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -125)
                            {
                                ld.CELL125 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -150)
                            {
                                ld.CELL150 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -175)
                            {
                                ld.CELL175 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -200)
                            {
                                ld.CELL200 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -225)
                            {
                                ld.CELL225 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -250)
                            {
                                ld.CELL250 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -275)
                            {
                                ld.CELL275 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -300)
                            {
                                ld.CELL300 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -325)
                            {
                                ld.CELL325 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -350)
                            {
                                ld.CELL350 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -375)
                            {
                                ld.CELL375 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -400)
                            {
                                ld.CELL400 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -425)
                            {
                                ld.CELL425 = lensDetail.Qty;
                            }
                            if (lensDetail.CYL == -450)
                            {
                                ld.CELL450 = lensDetail.Qty;
                            }

                        }
                        detail.LensDetails.Add(ld);
                    }
                    bulkOrderInvoice.Details.Add(detail);
                }
                bulkOrderInvoices.Add(bulkOrderInvoice);
            }
            return bulkOrderInvoices;
        }
        /// <summary>
        /// 导入单光毛料数据转换为BulkOrderDetail_MonofocalMaterialDetailResponses
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public List<BulkOrderDetail_MonofocalMaterialDetailResponse> ImportMonofocalMaterial(IFormFile file)
        {
            Stream stream = file.OpenReadStream();
            return ExcelHelper.ExcelStreamToDataTable(stream).ToList<BulkOrderDetail_MonofocalMaterialDetailResponse>();
        }
        /// <summary>
        /// 导入双光毛料数据转换为BulkOrderDetail_BifocalMaterialDetailResponses
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        public List<BulkOrderDetail_BifocalMaterialDetailResponse> ImportBifocalMaterial(IFormFile file)
        {
            Stream stream = file.OpenReadStream();
            var workbook = stream.ToXLWorkbook();
            List<BulkOrderDetail_BifocalMaterialDetailResponse> list = new();
            var sheet = workbook.Worksheets.FirstOrDefault();
            //ExcelHelper.IsXYTable(sheet);
            if (sheet != null)
            {
                var headerRow = sheet.Row(2);
                int cellCount = sheet.LastCellUsed().Address.ColumnNumber;
                int rowCount = sheet.LastRowUsed().RowNumber();
                var rowBase = sheet.Row(1);
                for (int i = 2; i <= cellCount; i++)
                {
                    string baseName = rowBase.Cell(i).GetString();
                    if (baseName == "")
                        baseName = rowBase.Cell(i - 1).GetString();
                    string eyeType = headerRow.Cell(i).GetString();
                    if (baseName.ToLower() == "total")
                        break;
                    for (int j = 3; j <= rowCount; j++)
                    {
                        var row = sheet.Row(j);
                        string add = row.Cell(1).GetString();
                        //XLDataType.
                        string qty = row.Cell(i).GetString();
                        if (add.ToLower() == "total")
                            break;
                        BulkOrderDetail_BifocalMaterialDetailResponse model = new()
                        {
                            EyeType = eyeType,
                            ADD = add.ToInt(),
                            Base = baseName.ToInt(),
                            Qty = qty == "" ? 0 : qty.ToInt()
                        };
                        list.Add(model);
                    }
                }
            }

            return list;
        }
        private IQueryable<BulkOrder> Where(PageRequest<BulkOrderRequest> request)
        {
            IQueryable<BulkOrder> query = _db.BulkOrder
                .Include(x => x.Customer).Include(x => x.Contract)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.BulkOrderDetail_LensDetails)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.BulkOrderDetail_MonofocalMaterialDetails)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.BulkOrderDetail_BifocalMaterialDetails)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Supplier)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Warehouse)
                .Include(x => x.BulkOrderDetails).ThenInclude(x => x.Contract_Lens).ThenInclude(x => x!.Lens).AsNoTracking();
            if (!request.Query.Number.IsNullOrWhiteSpace())
            {
                query = query.Where(x => x.Number.Contains(request.Query.Number!));
            }
            if (!request.Query.CustomerOrderNumber.IsNullOrWhiteSpace())
            {
                query = query.Where(x => x.CustomerOrderNumber.Contains(request.Query.CustomerOrderNumber!));
            }
            if (!request.Query.OrderRef.IsNullOrWhiteSpace())
            {
                query = query.Where(x => x.OrderRef.Contains(request.Query.OrderRef!));
            }
            if (!request.Query.TrayNumber.IsNullOrWhiteSpace())
            {
                query = query.Where(x => x.TrayNumber!.Contains(request.Query.TrayNumber!));
            }
            if (request.Query.InvoicePrintOnStart != null)
            {
                query = query.Where(x => x.InvoicePrintOn >= request.Query.InvoicePrintOnStart);
            }
            if (request.Query.InvoicePrintOnEnd != null)
            {
                query = query.Where(x => x.InvoicePrintOn <= request.Query.InvoicePrintOnEnd);
            }
            if (!request.Query.CreatedByName.IsNullOrWhiteSpace())
            {
                query = query.Where(x => x.CreatedByName.Contains(request.Query.CreatedByName!));
            }
            if (request.Query.CreatedOnStart != null)
            {
                query = query.Where(x => x.CreatedOn >= request.Query.CreatedOnStart.Value.ToStart());
            }
            if (request.Query.CreatedOnEnd != null)
            {
                query = query.Where(x => x.CreatedOn <= request.Query.CreatedOnEnd.Value.ToEnd());
            }
            if (request.Query.CustomerId != null)
            {
                query = query.Where(x => x.CustomerId == request.Query.CustomerId);
            }
            if (request.Query.SupplierId != null)
            {
                query = query.Where(x => x.BulkOrderDetails.Select(s => s.SupplierId).Contains(request.Query.SupplierId.Value));
            }
            if (request.Query.WarehouseId != null)
            {
                query = query.Where(x => x.BulkOrderDetails.Select(s => s.WarehouseId).Contains(request.Query.WarehouseId.Value));
            }
            if (request.Query.LensId != null)
            {
                query = query.Where(x => x.BulkOrderDetails.Select(s => s.Contract_Lens!.LensId).Contains(request.Query.LensId.Value));
            }
            if (!request.Query.Status.IsNullOrWhiteSpace())
            {
                query = query.Where(x => x.Status == request.Query.Status);
            }
            //if (request.Query.Status == BulkOrderStatusEnum.Pending.ToString())
            //{
            //    query = query.OrderByDescending(x => x.CreatedOn);
            //}
            //else if (request.Query.Status == BulkOrderStatusEnum.Delivery.ToString())
            //{
            //    query = query.OrderByDescending(x => x.CheckedOn);
            //}
            //else if (request.Query.Status == BulkOrderStatusEnum.Finished.ToString())
            //{
            //    query = query.OrderByDescending(x => x.FinishedOn);
            //}
            //else
            //{
            //    query = query.OrderByDescending(x => x.UpdatedOn);
            //}
            query = query.OrderByDescending(x => x.CreatedOn);
            return query;
        }
        private async Task Check(BulkOrder order)
        {
            if (order.Type == BulkOrderTypeEnum.Product.ToString())
            {
                foreach (var detail in order.BulkOrderDetails)
                {
                    var stock = await _db.Stock.GetByWarehouseLens(detail.WarehouseId, detail.Contract_Lens!.LensId);
                    foreach (var lensDetail in detail.BulkOrderDetail_LensDetails)
                    {
                        var stockDetail = stock.GetByRange(lensDetail.DegreesType, lensDetail.SPH, lensDetail.CYL);
                        if (stockDetail.Qty - lensDetail.Qty < 0)
                        {
                            throw new ApiException($"批量订单号{order.Number}的仓库{detail.Warehouse?.Code}镜种{detail.Contract_Lens.Lens?.SalesLensCode}度数SPH:{lensDetail.SPH},CYL{lensDetail.CYL}库存片数不足扣库");
                        }
                    }
                }
            }
            order.Status = BulkOrderStatusEnum.Delivery.ToString();
            order.CheckedBy = _currentUser.Id;
            order.CheckedByName = _currentUser.Name;
            order.CheckedOn = DateTime.Now;
        }
    }
}